-- Copyright (c) 2015 Oracle
--
-- Permission is hereby granted, free of charge, to any person obtaining
-- a copy of this software and associated documentation files (the
-- "Software"), to deal in the Software without restriction, including
-- without limitation the rights to use, copy, modify, merge, publish,
-- distribute, sublicense, and/or sell copies of the Software, and to
-- permit persons to whom the Software is furnished to do so, subject to
-- the following conditions:
-- 
-- The above copyright notice and this permission notice shall be
-- included in all copies or substantial portions of the Software.
-- 
-- THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
-- EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
-- MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
-- NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
-- LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
-- OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
-- WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
--
-- 
-- Some comments about the input data/control file are in order here:
--
-- (1) The input data fields have been arranged such that they each
-- are terminated by a comma and may be enclosed by quotes.  This has been
-- done solely for readability purposes; there is no requirement that the
-- data be arranged in this fashion. In fact, if we were loading large amounts
-- of data, it would be better to use a different field layout since field
-- terminators and enclosures are a drain on performance. 
--
-- (2) The LONG column, press_release, can accomodate a lerge amount of data.
-- In this script room for 10000 bytes of press release data has been made
-- available for each record.  This number is an estimate of the largest
-- amount that can be processed for this script.  If more data is added to
-- a press release field such that it uses more than 10000 bytes, then simply
-- increase the size in the field definition. 
--
-- (3) The CONTINUEIF clause has been used for readability purposes. It allows
-- us to arrange the data so that it multiple physical records, which can fit
-- nicely within an 80-column record, can be grouped together into one large
-- logical record.  The press_release fields contain a moderate amount of
-- textual data and it is easier to read and modify if we can see it all on
-- one line.  Of course this is possible because we can control the format
-- of the input data. In cases where the input data is generated by some
-- application there may be little or no control over this format. The first
-- record in a set of physical records has a space in the first byte of the
-- record. All other physical records that form the single logical record
-- has a '+' in the first byte of the record. Notice that the use of a 
-- CONTINUEIF clause doesn't affect absolute positioning. That is, we have
-- used one byte for the continuation character but we pretend that this
-- character is not there when we use the POSITION keyword; we still start
-- the numbering at 1.
--
-- (4) The INTO TABLE clauses use the INSERT mode which requires that the 
-- target tables be empty before loading.  This is done to safeguard against
-- inadvertently losing data. If requirements change, then other modes such
-- as REPLACE or APPEND may be used by replaciing the INSERT keyword with
-- one of the aforementioned modes.
--

LOAD DATA
CONTINUEIF NEXT(1) = '+'

INTO TABLE long2lob
INSERT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
 product_id           CHAR,
 press_release        CHAR(10000)
)

